/*
 *	Michael Harper-Jones
 *	9 June 2012
 *	Select all invocations available to a character to learn.
 *	Known invocations are also returned.
 */

create procedure [hqpad-character-allowed-invocations]
(
	@guilds varchar(max),
	@maxRanks varchar(max),
	@rulesId int,
	@knownList nvarchar(max)
)
as
begin
	declare @guildRankTable Table
	(
		Guild varchar(50),
		MaxRank int
	)

	insert @guildRankTable (Guild, MaxRank)
	select x.Item, convert(int, y.Item)
	from udf_ItemParse(@guilds, ',') x 
	inner join udf_ItemParse(@maxRanks, ',') y on y.Pos = x.Pos

	select i.*, g.Name as [SectName], g.Sphere, g.Slug as [SectSlug]
	from dbo.Invocation i
	inner join InvocationGuild ig on ig.Invocation_InvocationID = i.InvocationID
	inner join Guild g on g.GuildID = ig.Guild_GuildID
	inner join @guildRankTable gr on g.Slug = gr.Guild and i.[Rank] <= gr.MaxRank
	where IsCommon = 0
	and i.RuleSetID = @rulesId
	and (
		i.RequiredGroupName is null
		or
		i.RequiredGroupName in (select Item from udf_ItemParse(@knownList, ','))
		or
		i.GroupName in (select Item from udf_ItemParse(@knownList, ','))
	)

end